import pyecharts.options as opts
from pyecharts.charts import Scatter
import xlrd
import pyecharts.options as opts
data=xlrd.open_workbook(r'C:\Users\chumenliuxiang\Desktop\实验室作业\第一问\第一问.xls','r')
sheet_num=data.nsheets
# print(sheet_num)
biao1=data.sheet_by_name('Sheet1')
# print(biao1)
hours=biao1.col_values(0)
scores=biao1.col_values(1)
# print(hours)
# print(scores)
hours=hours[1:]
# print(hours)
scores=scores[1:]
# print(scores)
#————基础数据————
h0=h1=h2=h3=h4=h5=h6=h7=h8=h9=0
for i in hours:
    if i<=1:
        h0=h0+1
    elif i<=2:
        h1+=1
    elif i<=3:
        h2+=1
    elif i<=4:
        h3+=1
    elif i<=5:
        h4+=1
    elif i<=6:
        h5+=1
    elif i<=7:
        h6+=1
    elif i<=8:
        h7+=1
    elif i<=9:
        h8+=1
    elif i<=10:
        h9+=1
student1=[]
student1.append(h0)
student1.append(h1)
student1.append(h2)
student1.append(h3)
student1.append(h4)
student1.append(h5)
student1.append(h6)
student1.append(h7)
student1.append(h8)
student1.append(h9)
# print(student1)
#————第二题h数据————

s0=s1=s2=s3=s4=s5=s6=s7=s8=s9=0
for i in scores:
    if i<=10:
        s0=s0+1
    elif i<=20:
        s1+=1
    elif i<=30:
        s2+=1
    elif i<=40:
        s3+=1
    elif i<=50:
        s4+=1
    elif i<=60:
        s5+=1
    elif i<=70:
        s6+=1
    elif i<=80:
        s7+=1
    elif i<=90:
        s8+=1
    elif i<=100:
        s9+=1
student2=[]
student2.append(s1)
student2.append(s2)
student2.append(s3)
student2.append(s4)
student2.append(s5)
student2.append(s6)
student2.append(s7)
student2.append(s8)
student2.append(s9)
#————第三题s数据————
a=b=c=d=e=0
for i in scores:
    if i<60:
        e=e+1
    elif i<70:
        d+=1
    elif i<80:
        c+=1
    elif i<90:
        b+=1
    elif i<=100:
        a+=1
student3=[]
student3.append(e)
student3.append(d)
student3.append(c)
student3.append(b)
student3.append(a)
# print(student3)
shuju=['E(0~60)','D(61~70)','C(71~80)','B(81~90)','A(91~100)']
# print(shuju)
#————第五、六题分数段数据————
#———————————————————————————————————————————数据处理完毕
from pyecharts.charts import PictorialBar
from pyecharts.globals import SymbolType
from pyecharts.charts import Grid#平行显示
import pyecharts.options as opts#操作
from pyecharts.charts import Scatter#点
from pyecharts.charts import Bar#柱
from pyecharts.charts import Boxplot#箱
from pyecharts.charts import Pie#饼
#————————————————————————————————————————引入完毕
diyiwen=(
    Scatter()
    .add_xaxis(xaxis_data=hours)
    .add_yaxis(
        series_name="",
        y_axis=scores,
        label_opts=opts.LabelOpts(is_show=False),
    )
    .set_series_opts()
    .set_global_opts(
        xaxis_opts=opts.AxisOpts(
            type_="value", splitline_opts=opts.SplitLineOpts(is_show=True)
        ),
        yaxis_opts=opts.AxisOpts(
            type_="value",
            axistick_opts=opts.AxisTickOpts(is_show=True),
            splitline_opts=opts.SplitLineOpts(is_show=True),
        ),
        tooltip_opts=opts.TooltipOpts(is_show=False),
        legend_opts = opts.LegendOpts(is_show = False)
    )
)
#——————————————————————————————————————————————————————————————————————————————————————————————第一问
values = student1
hour = ["学习0~1小时", "学习1~2小时", "学习2~3小时", "学习3~4小时", "学习4~5小时", "学习5~6小时", "学习6~7小时", "学习7~8小时", "学习8~9小时", "学习9~10小时"]
dierwen=(
    PictorialBar()
    .add_xaxis(hour)
    .add_yaxis(
        "",
        values,
        label_opts=opts.LabelOpts(is_show=False),
        symbol_size=18,
        symbol_repeat="fixed",
        symbol_offset=[0, 0],
        is_symbol_clip=True,
        symbol=SymbolType.ROUND_RECT,
    )
    .reversal_axis()
    .set_global_opts(
        xaxis_opts=opts.AxisOpts(is_show=False),
        yaxis_opts=opts.AxisOpts(
            axistick_opts=opts.AxisTickOpts(is_show=False),
            axisline_opts=opts.AxisLineOpts(
                linestyle_opts=opts.LineStyleOpts(opacity=0)
            ),
        ),
        legend_opts = opts.LegendOpts(is_show = False)
    )
)
#——————————————————————————————————————————————————————————————————————第二问
disanwen=(
    Bar()
    .add_xaxis(
        [
            "10~20",
            "20~30",
            "30~40",
            "40~50",
            "50~60",
            "60~70",
            "70~80",
            "80~90",
            "90~100"
        ]
    )
    .add_yaxis("scores",student2)
    .set_global_opts(
        legend_opts = opts.LegendOpts(is_show = False),
        xaxis_opts=opts.AxisOpts(axislabel_opts=opts.LabelOpts(rotate=-15)),
    )
)
#————————————————————————————————————————————————————————————————————————————第三问
v1 = [hours]
disiwen=(Boxplot()
    .add_xaxis(["student"])
    .add_yaxis("hours", Boxplot().prepare_data(v1))
    .set_global_opts(legend_opts = opts.LegendOpts(is_show = False),)
    )
#——————————————————————————————————————————————————————————————————————————第四问
diwuwen=(
    Pie()
    .add("", [list(z) for z in zip(shuju, student3)],radius=["0%", "35%"],center=["50%", "70%"])
    .set_series_opts(label_opts=opts.LabelOpts(formatter="{b}: {c}"))
    .set_global_opts(legend_opts = opts.LegendOpts(is_show = False),)
)
#————————————————————————————————————————————————————————————————第五问
diliuwen=(
    Bar()
    .add_xaxis(shuju)
    .add_yaxis("成绩",student3)
    .set_global_opts(
        yaxis_opts=opts.AxisOpts(name="学生人数"),
        xaxis_opts=opts.AxisOpts(name="分数等级"),
        legend_opts = opts.LegendOpts(is_show = False)
    )
)
#————————————————————————————————————————————————————————————第六问

grid = Grid(init_opts=opts.InitOpts(height="1200px", width="1600px"))

grid.add(diyiwen,grid_opts=opts.GridOpts(pos_left=10,pos_top=10,width=400,height=500))
grid.add(dierwen,grid_opts=opts.GridOpts(pos_left=600,pos_top=10,width=400,height=500))
grid.add(disanwen,grid_opts=opts.GridOpts(pos_left=1190,pos_top=10,width=400,height=500))
grid.add(disiwen,grid_opts=opts.GridOpts(pos_left=10,pos_top=610,width=400,height=500))

grid.add(diliuwen,grid_opts=opts.GridOpts(pos_left=1190,pos_top=610,width=400,height=500))
grid.add(diwuwen,grid_opts=opts.GridOpts(pos_left=600,pos_top=610,width=400,height=500))
grid.render()